{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "I79JKrXNNb67"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "\n",
        "df = pd.read_excel('test_excel.xlsx')\n",
        "pivot_df = df.pivot_table(index='ID', columns=df.groupby('ID').cumcount().add(1), values='X', fill_value=0)\n",
        "pivot_df.columns = [f'Center_X{i}' for i in range(1, len(pivot_df.columns) + 1)]\n",
        "pivot_df.to_excel('converted_data_x_test.xlsx')\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "\n",
        "df = pd.read_excel('test_excel.xlsx')\n",
        "pivot_df = df.pivot_table(index='ID', columns=df.groupby('ID').cumcount().add(1), values='Y', fill_value=0)\n",
        "pivot_df.columns = [f'Center_Y{i}' for i in range(1, len(pivot_df.columns) + 1)]\n",
        "pivot_df.to_excel('converted_data_Y_test.xlsx')"
      ],
      "metadata": {
        "id": "lICOW-LxnG0v"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "\n",
        "sheet1 = pd.read_excel('converted_data_x_test.xlsx')\n",
        "sheet2 = pd.read_excel('converted_data_Y_test.xlsx')\n",
        "\n",
        "merged_df = pd.merge(sheet1, sheet2, on='ID')\n",
        "\n",
        "# Creating a new dataframe with alternate columns from sheet1 and sheet2\n",
        "result_df = pd.DataFrame()\n",
        "\n",
        "# Assuming that the 'id' column is the first column in both sheets\n",
        "result_df['ID'] = merged_df['ID']\n",
        "\n",
        "\n",
        "for col in range(1, len(sheet1.columns)):\n",
        "     result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
        "     result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
        "\n",
        "result_df.to_excel(\"xy_test.xlsx\", index=False)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "n8xYHcODvuKx",
        "outputId": "7527e4c9-4531-4593-b1b3-50b67eb72d14"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:16: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
            "<ipython-input-4-fee1a5ed0499>:17: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`\n",
            "  result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "\n",
        "sheet1 = pd.read_excel('converted_data_x_8.xlsx')\n",
        "sheet2 = pd.read_excel('converted_data_for_Y_cordinates8.xlsx')\n",
        "\n",
        "merged_df = pd.merge(sheet1, sheet2, on='ID')\n",
        "\n",
        "# Creating a new dataframe with alternate columns from sheet1 and sheet2\n",
        "result_df = pd.DataFrame()\n",
        "\n",
        "# Assuming that the 'id' column is the first column in both sheets\n",
        "result_df['ID'] = merged_df['ID']\n",
        "\n",
        "\n",
        "for col in range(1, len(sheet1.columns)):\n",
        "     result_df[f'Center_X{col}'] = merged_df[f'Center_X{col}']\n",
        "     result_df[f'Center_Y{col}'] = merged_df[f'Center_Y{col}']\n",
        "\n",
        "result_df.to_excel(\"xy8.xlsx\", index=False)"
      ],
      "metadata": {
        "id": "dAQcqkSz8Hdf"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}